package com.example.demo.controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.sql.ResultSetMetaData;
import org.json.JSONObject;

public class manifest {
    public static String url = "jdbc:mysql://47.101.50.45:3306/bs250088";
    public static String username = "bs250088";
    public static String password = "bs250088";

    // public static String proname = "demo";
    public static JSONObject jsonObject = new JSONObject();
    public static String biao = ""; // 本次对应的表
    public static String arr = "";
    static Statement statement = null;
    static Connection connection = null;

    public static String chuli(Map<String, Object> requestData) {

        String method = "";

        for (Map.Entry<String, Object> entry : requestData.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();
            // 在这里根据key和value做相应的处理
            // ...
            if (key == "method") {
                method = value.toString();
            } else if (key == "biao") {
                biao = value.toString();
            } else {
                arr = arr == "" ? key : (arr + "," + key);
                jsonObject.put(key, value);
            }
        }

        return method;

    }

    public static void lianjie() {
        try {
            connection = DriverManager.getConnection(url, username, password);
            statement = connection.createStatement();
        } catch (SQLException e) {
            System.out.println("数据库连接错误");
        }
    }

    public static void chongzhi() {
        arr = "";
        jsonObject.clear();
        try {
            connection.close();
            statement.close();
        } catch (SQLException e) {

        }
    }

    public static String caozuo(String sql) {
        JSONObject res = new JSONObject();
        try {

            int affectedRows = statement.executeUpdate(sql);
            if (affectedRows > 0) {
                System.out.println("Delete operation successful.");
                res.put("code", 200);
            } else {
                System.out.println("No records deleted.");
                res.put("code", 300);
            }

            System.out.println("SQL executed successfully.");
            return res.toString();
        } catch (SQLException e) {
            e.printStackTrace();
            res.put("code", 400);
            return res.toString();
        }
    }

    public static String charu(String biao, String arrstring) {
        JSONObject res = new JSONObject();

        // 创建sql语句
        String keys = "";
        String values = "";

        List<String> arr = Arrays.asList(arrstring.split(","));

        for (int i = 0; i < arr.size(); i++) {
            keys = i == 0 ? arr.get(0) : (keys + "," + arr.get(i));
            values = i == 0 ? ("'" + jsonObject.opt(arr.get(0)).toString() + "'")
                    : (values + ",'" + jsonObject.opt(arr.get(i)).toString() + "'");
        }
        String sql = "INSERT into " + biao + " (" + keys + ") VALUES (" + values + ")";

        System.out.println(sql);

        // 创建连接、执行 SQL
        try {

            // 执行 SQL,插入新数据
            int affectedRows = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            int id = 0;
            // 获取插入记录的 ID
            if (affectedRows > 0) {
                ResultSet generatedKeys = statement.getGeneratedKeys();
                if (generatedKeys.next()) {
                    id = generatedKeys.getInt(1);
                }
                res.put("code", 200);
                res.put("data", id);
            } else {
                res.put("code", 300);
                res.put("data", id);
            }

            System.out.println("SQL executed successfully.");
            return res.toString();
        } catch (SQLException e) {
            e.printStackTrace();
            res.put("code", 400);
            return res.toString();
        }
    }

    public static String xiugai(String biao, String arrstring, Integer id) {
        JSONObject res = new JSONObject();

        // 创建sql语句
        String sets = "";

        if (id == null) {
            id = jsonObject.getInt("id");
        }

        List<String> arr = Arrays.asList(arrstring.split(","));

        for (int i = 0; i < arr.size(); i++) {
            if (arr.get(i).equals("id"))
                continue;
            sets = sets == "" ? (arr.get(i) + " = '" + jsonObject.opt(arr.get(i)).toString() + "' ")
                    : (sets + " , " + arr.get(i) + " = '" + jsonObject.opt(arr.get(i)).toString() + "' ");

        }
        String sql = "UPDATE " + biao + " set " + sets + " where id = " + id + " limit 1";

        System.out.println(sql);

        // 创建连接、执行 SQL
        try {
            int affectedRows = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            if (affectedRows > 0) {
                System.out.println("Delete operation successful.");
                res.put("code", 200);
            } else {
                System.out.println("No records deleted.");
                res.put("code", 300);
            }

            System.out.println("SQL executed successfully.");
            return res.toString();
        } catch (SQLException e) {
            e.printStackTrace();
            res.put("code", 400);
            return res.toString();
        }
    }

    public static String shanchu(String biao, int id) {
        JSONObject res = new JSONObject();
        String sql = "DELETE FROM " + biao + " where id = " + id;

        try {

            // 执行 SQL
            int affectedRows = statement.executeUpdate(sql);

            // 判断是否执行成功
            if (affectedRows > 0) {
                System.out.println("Delete operation successful.");
                res.put("code", 200);
            } else {
                System.out.println("No records deleted.");
                res.put("code", 300);
            }

            System.out.println("SQL executed successfully.");
            return res.toString();
        } catch (SQLException e) {
            e.printStackTrace();
            res.put("code", 400);
            return res.toString();
        }
    }

    public static String chaxun(String sql) {
        JSONObject res = new JSONObject();
        System.out.println(sql);

        try {
            ResultSet resultSet = statement.executeQuery(sql);
            // 获取查询结果的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            // 获取查询结果的列数
            int columnCount = metaData.getColumnCount();

            // 创建List来存储查询结果
            List<String[]> resultList = new ArrayList<>();

            // 遍历结果集
            while (resultSet.next()) {
                // 创建数组来存储每一行的数据
                String[] row = new String[columnCount];

                // 遍历每一列，将字段值存储到数组中
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    String value = resultSet.getString(i);
                    row[i - 1] = columnName + ":" + value;
                }

                // 将每一行的数据数组添加到结果列表中
                resultList.add(row);
            }

            // 将List转换为二维数组
            String[][] resultArray = resultList.toArray(new String[0][0]);
            Object[] r = new Object[resultArray.length];
            // 遍历原始数组并进行转换
            for (int i = 0; i < resultArray.length; i++) {
                String[] innerArray = resultArray[i];
                // 创建一个用于存储转换结果的Map对象
                java.util.Map<String, String> map = new java.util.HashMap<>();

                // 遍历内部数组并将键值对添加到Map中
                for (String item : innerArray) {
                    String[] keyValue = item.split(":");
                    if (keyValue.length >= 2) {
                        String k2 = "";
                        if (keyValue.length > 2) {
                            for (int j = 1; j < keyValue.length; j++) {
                                k2 = k2 == "" ? keyValue[j] : (k2 + ":" + keyValue[j]);
                            }
                        } else {
                            k2 = keyValue[1];
                        }
                        map.put(keyValue[0], k2);
                    }
                }

                // 将Map对象添加到结果数组中
                r[i] = map;
            }

            res.put("code", 200);
            res.put("data", r);
            return res.toString();
        } catch (SQLException e) {
            // e.printStackTrace();
            res.put("code", 400);
            return res.toString();
        }
    }

    public static Object[] chaxun_2(String sql) {
        System.out.println(sql);

        try {

            ResultSet resultSet = statement.executeQuery(sql);

            // 获取查询结果的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();

            // 获取查询结果的列数
            int columnCount = metaData.getColumnCount();

            // 创建List来存储查询结果
            List<String[]> resultList = new ArrayList<>();

            // 遍历结果集
            while (resultSet.next()) {
                // 创建数组来存储每一行的数据
                String[] row = new String[columnCount];

                // 遍历每一列，将字段值存储到数组中
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    String value = resultSet.getString(i);

                    row[i - 1] = columnName + ":" + value;
                }

                // 将每一行的数据数组添加到结果列表中
                resultList.add(row);
            }

            // 将List转换为二维数组
            String[][] resultArray = resultList.toArray(new String[0][0]);
            Object[] r = new Object[resultArray.length];
            // 遍历原始数组并进行转换
            for (int i = 0; i < resultArray.length; i++) {
                String[] innerArray = resultArray[i];
                // 创建一个用于存储转换结果的Map对象
                java.util.Map<String, String> map = new java.util.HashMap<>();

                // 遍历内部数组并将键值对添加到Map中
                for (String item : innerArray) {
                    String[] keyValue = item.split(":");
                    if (keyValue.length >= 2) {
                        String k2 = "";
                        if (keyValue.length > 2) {
                            for (int j = 1; j < keyValue.length; j++) {
                                k2 = k2 == "" ? keyValue[j] : (k2 + ":" + keyValue[j]);
                            }
                        } else {
                            k2 = keyValue[1];
                        }
                        map.put(keyValue[0], k2);
                    }
                }

                // 将Map对象添加到结果数组中
                r[i] = map;
            }

            // 返回结果数组
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 如果发生异常，返回null
        return null;
    }

    public static int chaxun_num(String sql) {
        System.out.println(sql);

        try {
            ResultSet resultSet = statement.executeQuery(sql);
            // 获取查询结果的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            // 获取查询结果的列数
            int columnCount = metaData.getColumnCount();
            // 创建List来存储查询结果
            List<String[]> resultList = new ArrayList<>();
            // 遍历结果集
            while (resultSet.next()) {
                // 创建数组来存储每一行的数据
                String[] row = new String[columnCount];
                // 遍历每一列，将字段值存储到数组中
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    String value = resultSet.getString(i);
                    row[i - 1] = columnName + ":" + value;
                }
                // 将每一行的数据数组添加到结果列表中
                resultList.add(row);
            }
            return resultList.size();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    public static String chaxun_one(String sql) {
        try {
            ResultSet resultSet = statement.executeQuery(sql);
            // 获取查询结果的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            // 获取查询结果的列数
            int columnCount = metaData.getColumnCount();
            // 创建List来存储查询结果
            List<String[]> resultList = new ArrayList<>();

            // 遍历结果集
            while (resultSet.next()) {
                // 创建数组来存储每一行的数据
                String[] row = new String[columnCount];

                // 遍历每一列，将字段值存储到数组中
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    String value = resultSet.getString(i);
                    System.out.println(columnName);
                    System.out.println(value);
                    row[i - 1] = columnName + ":" + value;
                }

                // 将每一行的数据数组添加到结果列表中
                resultList.add(row);
            }

            // 将List转换为二维数组
            String[][] resultArray = resultList.toArray(new String[0][0]);
            Object[] r = new Object[resultArray.length];
            // 遍历原始数组并进行转换
            for (int i = 0; i < resultArray.length; i++) {
                String[] innerArray = resultArray[i];
                if (i > 1)
                    break;
                // 创建一个用于存储转换结果的Map对象
                java.util.Map<String, String> map = new java.util.HashMap<>();

                // 遍历内部数组并将键值对添加到Map中
                for (String item : innerArray) {
                    String[] keyValue = item.split(":");
                    if (keyValue.length >= 2) {
                        String k2 = "";
                        if (keyValue.length > 2) {
                            for (int j = 1; j < keyValue.length; j++) {
                                k2 = k2 == "" ? keyValue[j] : (k2 + ":" + keyValue[j]);
                            }
                        } else {
                            k2 = keyValue[1];
                        }
                        map.put(keyValue[0], k2);
                    }
                }

                // 将Map对象添加到结果数组中
                r[i] = map;
            }

            JSONObject j = new JSONObject();
            if (resultArray.length > 0) {
                j.put("code", 200);
                j.put("data", r[0]);
            } else {
                j.put("code", 300);
            }

            // 返回结果数组
            return j.toString();
        } catch (SQLException e) {
            System.out.println(sql);

            e.printStackTrace();
        }

        // 如果发生异常，返回null
        return null;
    }

    public static Object chaxun_one_2(String sql) {
        try {
            ResultSet resultSet = statement.executeQuery(sql);
            // 获取查询结果的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            // 获取查询结果的列数
            int columnCount = metaData.getColumnCount();
            // 创建List来存储查询结果
            List<String[]> resultList = new ArrayList<>();

            // 遍历结果集
            while (resultSet.next()) {
                // 创建数组来存储每一行的数据
                String[] row = new String[columnCount];

                // 遍历每一列，将字段值存储到数组中
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    String value = resultSet.getString(i);

                    row[i - 1] = columnName + ":" + value;
                }

                // 将每一行的数据数组添加到结果列表中
                resultList.add(row);
            }

            // 将List转换为二维数组
            String[][] resultArray = resultList.toArray(new String[0][0]);
            Object[] r = new Object[resultArray.length];
            // 遍历原始数组并进行转换
            for (int i = 0; i < resultArray.length; i++) {
                String[] innerArray = resultArray[i];
                if (i > 1)
                    break;
                // 创建一个用于存储转换结果的Map对象
                java.util.Map<String, String> map = new java.util.HashMap<>();

                // 遍历内部数组并将键值对添加到Map中

                for (String item : innerArray) {
                    String[] keyValue = item.split(":");
                    if (keyValue.length >= 2) {
                        String k2 = "";
                        if (keyValue.length > 2) {
                            for (int j = 1; j < keyValue.length; j++) {
                                k2 = k2 == "" ? keyValue[j] : (k2 + ":" + keyValue[j]);
                            }
                        } else {
                            k2 = keyValue[1];
                        }
                        map.put(keyValue[0], k2);
                    }
                }

                // 将Map对象添加到结果数组中
                r[i] = map;
            }

            return r[0];
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 如果发生异常，返回null
        return null;
    }

    public static String youwu(String biao, String str) {
        List<String> arr = Arrays.asList(str.split(","));
        String tjsql = "";
        String res = "";
        if (arr.size() > 0) {
            for (int i = 0; i < arr.size(); i++) {
                String s = arr.get(i) + " = " + manifest.jsonObject.get(arr.get(i));
                tjsql = tjsql == "" ? (" where " + s) : (tjsql + "  and " + s);
            }
        }
        String sql = """
                SELECT * FROM %s %s
                """.formatted(biao, tjsql);
        int num = chaxun_num(sql);
        if (num == 0) {
            res = charu(biao, str);
        } else {
            int id = gongju.ogetint(manifest.chaxun_one_2(sql), "id");
            res = shanchu(biao, id);
        }
        return res;
    }
}
